﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.SqlTypes;
using ELS.User;

namespace ELS.Template
{
    public class TemplateDataAccess
    {
        /// <summary>
        /// The connection string
        /// </summary>
        private static string connectionString = UserCommon.connectionString;

        private static string insertTemplateString = @"INSERT INTO [dbo].[TemplateDocument] ([TITLE],[DESCRIPTION],[DOCUMENT_TYPE_ID],[CREATED_BY]
                                                    ,[MODIFIED_BY],[CREATED_DATE],[MODIFIED_DATE])
                                                    VALUES (@title, @description, @type, @createdBy, @modifiedBy, @createdDate, @modifiedDate); 
                                                    SELECT SCOPE_IDENTITY()";

        private static string insertCategoryString = @"INSERT INTO [dbo].[TemplateQuestionCategory] ([TITLE],[DESCRIPTION])
                                                    VALUES (@title, @description); SELECT SCOPE_IDENTITY()";
        
        private static string insertQuestionString = @"INSERT INTO [dbo].[TemplateQuestion] ([CONTENT], [QUESTION_NO], [QUESTION_TYPE_ID])
                                                    VALUES (@content, @questionNo, @typeId); SELECT SCOPE_IDENTITY()";
        
        private static string insertAnswerString = @"INSERT INTO [dbo].[TemplateAnswer] ([CONTENT],[ANSWER_TYPE_ID])
                                                    VALUES (@content, @typeId); SELECT SCOPE_IDENTITY()";

        private static string insertQuestionAnswerRelationshipString = @"INSERT INTO [dbo].[TemplateQuestionTemplateAnswer] ([TEMPLATE_QUESTION_ID],[TEMPLATE_ANSWER_ID], [ORDER_IN_QUESTION])
                                                    VALUES (@questionId, @answerId, @order); SELECT SCOPE_IDENTITY()";

        private static string insertCategoryQuestionRelationshipString = @"INSERT INTO [dbo].[TemplateQuestionCategoryTemplateQuestion] ([TEMPLATE_QUESTION_CATEGORY_ID],[TEMPLATE_QUESTION_ID], [ORDER_IN_CATEGORY])
                                                    VALUES (@categoryId, @questionId, @order); SELECT SCOPE_IDENTITY()";

        private static string countAllTemplatesString = @"SELECT COUNT (*) as TOTAL FROM [TemplateDocument]";        
        
        internal int CountAllTemplates()
        {
            int intCount = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    DataTable dtTable = new DataTable();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = countAllTemplatesString;

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlComm);
                    dtAdapter.Fill(dtTable);
                    intCount = (int)dtTable.Rows[0]["TOTAL"];
                    return intCount;
                }
                catch (SqlException e)
                {
                    return intCount;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int CountAllTemplatesByKeyword(string keyword)
        {
            int intCount = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    DataTable dtTable = new DataTable();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = @"SELECT COUNT (*) as TOTAL FROM [TemplateDocument] where [TemplateDocument].[TITLE] like '%"+keyword+"%'";

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlComm);
                    dtAdapter.Fill(dtTable);
                    intCount = (int)dtTable.Rows[0]["TOTAL"];
                    return intCount;
                }
                catch (SqlException e)
                {
                    return intCount;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal DataTable GetAllTemplatesPaging(int mintPageSize, int intSkip)
        {
            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {                    
                    StringBuilder query = new StringBuilder();
                    query.Append("SELECT TOP " + mintPageSize + " * FROM ")
                        .Append("(SELECT TemplateItem.TemplateID, TemplateItem.TemplateTitle, TemplateItem.TemplateDescription, TemplateItem.DocumentTitle,")
                        .Append(" TemplateItem.CreatedBy, TemplateItem.CREATED_DATE,d.USERNAME as ModifiedBy, TemplateItem.MODIFIED_DATE, ROW_NUMBER() OVER (ORDER BY TemplateItem.TemplateTitle) AS num")
                        .Append(" FROM (SELECT a.[ID] as TemplateID, a.[TITLE] as TemplateTitle, a.[DESCRIPTION] as TemplateDescription, b.[TITLE] as DocumentTitle,")
                        .Append(" c.[USERNAME] as CreatedBy, a.[CREATED_DATE], a.[MODIFIED_BY], a.[MODIFIED_DATE] ")
                        .Append(" FROM [dbo].[TemplateDocument] AS a, [dbo].[DocumentType] AS b, [dbo].[USER] as c ")
	                    .Append(" WHERE (a.[DOCUMENT_TYPE_ID] = b.[ID] AND a.[CREATED_BY] = c.[ID])) as TemplateItem ")
                        .Append(" left join [dbo].[USER] as d on d.ID = TemplateItem.MODIFIED_BY) AS TemplateList ")
                        .Append(" WHERE num >" + intSkip);

                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@query.ToString(), sqlConnection);
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dt);
                }
                catch (SqlException e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return dt;
        }

        internal DataTable GetAllTemplatesByKeywordPaging(string keyword, int mintPageSize, int intSkip)
        {
            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {                    
                    StringBuilder query = new StringBuilder();
                    query.Append("SELECT TOP " + mintPageSize + " * FROM ")
                        .Append("(SELECT TemplateItem.TemplateID, TemplateItem.TemplateTitle as TemplateTitle, TemplateItem.TemplateDescription, TemplateItem.DocumentTitle,")
                        .Append(" TemplateItem.CreatedBy, TemplateItem.CREATED_DATE,d.USERNAME as ModifiedBy, TemplateItem.MODIFIED_DATE, ROW_NUMBER() OVER (ORDER BY TemplateItem.TemplateTitle) AS num")
                        .Append(" FROM (SELECT a.[ID] as TemplateID, a.[TITLE] as TemplateTitle, a.[DESCRIPTION] as TemplateDescription, b.[TITLE] as DocumentTitle,")
                        .Append(" c.[USERNAME] as CreatedBy, a.[CREATED_DATE], a.[MODIFIED_BY], a.[MODIFIED_DATE] ")
                        .Append(" FROM [dbo].[TemplateDocument] AS a, [dbo].[DocumentType] AS b, [dbo].[USER] as c ")
                        .Append(" WHERE (a.[DOCUMENT_TYPE_ID] = b.[ID] AND a.[CREATED_BY] = c.[ID])) as TemplateItem ")
                        .Append(" left join [dbo].[USER] as d on d.ID = TemplateItem.MODIFIED_BY) AS TemplateList ")
                        .Append(" WHERE TemplateTitle like '%" + keyword + "%' AND num >" + intSkip);

                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@query.ToString(), sqlConnection);
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dt);
                }
                catch (SqlException e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return dt;
        }

        internal int InsertTemplate(TemplateDocument td)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                int newId;
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = insertTemplateString;

                    sqlComm.Parameters.Add("@title", SqlDbType.NVarChar).Value = td.Title;
                    sqlComm.Parameters.Add("@description", SqlDbType.NVarChar).Value = td.Description;
                    sqlComm.Parameters.Add("@type", SqlDbType.Int).Value = td.DocumentTypeId;
                    sqlComm.Parameters.Add("@createdBy", SqlDbType.Int).Value = td.CreatedBy;
                    sqlComm.Parameters.Add("@modifiedBy", SqlDbType.Int).Value = td.ModifiedBy;
                    sqlComm.Parameters.Add("@createdDate", SqlDbType.DateTime).Value = new SqlDateTime(DateTime.Now);
                    sqlComm.Parameters.Add("@modifiedDate", SqlDbType.DateTime).Value = new SqlDateTime(DateTime.Now);

                    newId = (int)((Decimal)sqlComm.ExecuteScalar());
                }
                catch (SqlException e)
                {
                    return -1;
                }
                finally
                {
                    sqlConnection.Close();
                }
                return newId;
            }
        }

        internal int InsertCategory(string title, string desctiption)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                int newId;
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = insertCategoryString;

                    sqlComm.Parameters.Add("@title", SqlDbType.NVarChar).Value = title;
                    sqlComm.Parameters.Add("@description", SqlDbType.NVarChar).Value = desctiption;

                    newId = (int)((Decimal)sqlComm.ExecuteScalar());
                }
                catch (SqlException e)
                {
                    return -1;
                }
                finally
                {
                    sqlConnection.Close();
                }
                return newId;
            }
        }

        internal int InsertQuestion(string content, string questionNo, int typeId)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                int newId;
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = insertQuestionString;

                    sqlComm.Parameters.Add("@content", SqlDbType.NVarChar).Value = content;
                    sqlComm.Parameters.Add("@questionNo", SqlDbType.NVarChar).Value = questionNo;
                    sqlComm.Parameters.Add("@typeId", SqlDbType.Int).Value = typeId;

                    newId = (int)((Decimal)sqlComm.ExecuteScalar());
                }
                catch (SqlException e)
                {
                    return -1;
                }
                finally
                {
                    sqlConnection.Close();
                }
                return newId;
            }
        }

        internal int InsertAnswer(string content, int typeId)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                int newId;
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = insertAnswerString;

                    sqlComm.Parameters.Add("@content", SqlDbType.NVarChar).Value = content;
                    sqlComm.Parameters.Add("@typeId", SqlDbType.Int).Value = typeId;

                    newId = (int)((Decimal)sqlComm.ExecuteScalar());
                }
                catch (SqlException e)
                {
                    return -1;
                }
                finally
                {
                    sqlConnection.Close();
                }
                return newId;
            }
        }

        internal int InsertCategoryQuestionRelationship(int categoryId, int questionId, int orderInCategory)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                int newId;
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = insertCategoryQuestionRelationshipString;

                    sqlComm.Parameters.Add("@categoryId", SqlDbType.Int).Value = categoryId;
                    sqlComm.Parameters.Add("@questionId", SqlDbType.Int).Value = questionId;
                    sqlComm.Parameters.Add("@order", SqlDbType.Int).Value = orderInCategory;

                    newId = (int)((Decimal)sqlComm.ExecuteScalar());
                }
                catch (SqlException e)
                {
                    return -1;
                }
                finally
                {
                    sqlConnection.Close();
                }
                return newId;
            }
        }

        internal int InsertQuestionAnswerRelationship(int questionId, int answerId, int orderInQuestion)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                int newId;
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = insertQuestionAnswerRelationshipString;

                    sqlComm.Parameters.Add("@questionId", SqlDbType.Int).Value = questionId;
                    sqlComm.Parameters.Add("@answerId", SqlDbType.Int).Value = answerId;
                    sqlComm.Parameters.Add("@order", SqlDbType.Int).Value = orderInQuestion;

                    newId = (int)((Decimal)sqlComm.ExecuteScalar());
                }
                catch (SqlException e)
                {
                    return -1;
                }
                finally
                {
                    sqlConnection.Close();
                }
                return newId;
            }
        }

        internal DataTable GetAllDocumentTypes()
        {
            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"SELECT [DocumentType].ID as DOCUMENT_TYPE_ID, [DocumentType].TITLE as DOCUMENT_TYPE_TITLE FROM DocumentType", sqlConnection);
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dt);
                }
                catch (SqlException e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return dt;
        }

        internal DataTable GetTemplateInfo(string selectedTemplateID)
        {
            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    StringBuilder query = new StringBuilder();
                    query.Append("SELECT a.[ID] as TemplateID, a.[TITLE] as TemplateTitle, a.[DESCRIPTION] as TemplateDescription, b.[ID] as DocumentTypeID")
                        .Append(" FROM [dbo].[TemplateDocument] AS a, [dbo].[DocumentType] AS b")
                        .Append(" WHERE a.[DOCUMENT_TYPE_ID] = b.[ID] AND a.[ID] = " + selectedTemplateID);
                    sqlConnection.Open();

                    SqlCommand cmd = new SqlCommand(@query.ToString(), sqlConnection);
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dt);
                }
                catch (SqlException e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return dt;
        }

        internal int UpdateTemplate(TemplateDocument td)
        {
            int rowUpdate = -1;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = @"UPDATE [TemplateDocument] SET   
                                                                [TemplateDocument].TITLE = @title,
                                                                [TemplateDocument].DESCRIPTION = @description,
                                                                [TemplateDocument].DOCUMENT_TYPE_ID = @documentTypeID,
                                                                [TemplateDocument].MODIFIED_BY = @modifiedBy,
                                                                [TemplateDocument].MODIFIED_DATE = @modifiedDate
                                             WHERE [TemplateDocument].ID = @templateID";
                    sqlComm.Parameters.Add("@templateID", SqlDbType.Int).Value = td.Id;
                    sqlComm.Parameters.Add("@title", SqlDbType.NVarChar).Value = td.Title;
                    sqlComm.Parameters.Add("@description", SqlDbType.NVarChar).Value = td.Description;
                    sqlComm.Parameters.Add("@documentTypeID", SqlDbType.Int).Value = td.DocumentTypeId;
                    sqlComm.Parameters.Add("@modifiedBy", SqlDbType.Int).Value = td.ModifiedBy;
                    sqlComm.Parameters.Add("@modifiedDate", SqlDbType.DateTime).Value = new SqlDateTime(DateTime.Now);
                    rowUpdate = sqlComm.ExecuteNonQuery();

                    return rowUpdate;
                }
                catch (Exception e)
                {
                    return rowUpdate;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal DataTable GetPreviousQuestion(int currentQuestionId)
        {
            DataTable dtPreviousQuestion = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"SELECT * FROM [TemplateQuestion] WHERE [ID] = (SELECT MAX([ID]) FROM [TemplateQuestion] WHERE [ID] < @currentQuestionId)", sqlConnection);
                    cmd.Parameters.Add("@currentQuestionId", SqlDbType.Int).Value = currentQuestionId;
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtPreviousQuestion);
                    return dtPreviousQuestion;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal DataTable GetNextQuestion(int currentQuestionId)
        {
            DataTable dtPreviousQuestion = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"SELECT * FROM [TemplateQuestion] WHERE [ID] = (SELECT MIN([ID]) FROM [TemplateQuestion] WHERE [ID] > @currentQuestionId)", sqlConnection);
                    cmd.Parameters.Add("@currentQuestionId", SqlDbType.Int).Value = currentQuestionId;
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtPreviousQuestion);
                    return dtPreviousQuestion;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal DataTable GetAnswersByQuestionId(string questionId)
        {
            DataTable answerTable = new DataTable(); ;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"SELECT ta.[ID], ta.[CONTENT], ta.[ANSWER_TYPE_ID], tqta.[ORDER_IN_QUESTION]  FROM TemplateAnswer AS ta, TemplateQuestion AS tq, TemplateQuestionTemplateAnswer AS tqta WHERE ta.[ID] = tqta.[TEMPLATE_ANSWER_ID] AND tq.[ID] = tqta.[TEMPLATE_QUESTION_ID] AND tq.[ID] = @questionId ORDER BY tqta.ORDER_IN_QUESTION", sqlConnection);
                    cmd.Parameters.Add("@questionId", SqlDbType.Int).Value = questionId;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(answerTable);
                    return answerTable;
                }
                catch (Exception e)
                {
                    return answerTable;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int GetCategoryIdByTitle(string title, int templateId)
        {
            int categoryIdResult = -1;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = @"SELECT [TemplateQuestionCategory].[ID] FROM [TemplateQuestionCategory], [TemplateDocumentTemplateQuestionCategory] 
                                                                WHERE [TemplateQuestionCategory].[ID] = [TemplateDocumentTemplateQuestionCategory].[TEMPLATE_QUESTION_CATEGORY_ID] 
                                                                AND [TemplateDocumentTemplateQuestionCategory].[TEMPLATE_DOCUMENT_ID] = @templateId
                                                                AND [TemplateQuestionCategory].TITLE = @title";
                    sqlComm.Parameters.Add("@templateID", SqlDbType.Int).Value = templateId;
                    sqlComm.Parameters.Add("@title", SqlDbType.NVarChar).Value = title;

                    categoryIdResult = (int) sqlComm.ExecuteScalar();

                    return categoryIdResult;
                }
                catch (Exception e)
                {
                    return categoryIdResult;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }
    }
}
